package CelestialDataAccess;

//import com.microsoft.sqlserver.jdbc.*;
import com.sun.rowset.*;   //provides cached row set implementation
import javax.sql.rowset.*; //provides cached row set interface
import java.sql.*;         //provides connection object and sql exception
import java.util.List;
import java.util.Dictionary;

public abstract class DataAccessBase {

    protected static String strDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    protected static String strCnn = "jdbc:sqlserver://localhost\\SQLEXPRESS;Database=CelestialDB;integratedSecurity=true";
    protected static Connection cnn;

    /*************************************************************************/
    public void setConnectionString(String cnn_string){ strCnn = cnn_string; }
    public String getConnectionString(){ return strCnn; }
    /*************************************************************************/
   
    public static void open() throws DataAccessException{

        try{
            if(cnn != null && !cnn.isClosed()) return;

            //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //System.out.println("Driver loaded");

            //cnn = DriverManager.getConnection("jdbc:odbc:CelestialDB");
            //System.out.println("Database connected");

            Class.forName(strDriver);
            //System.out.println("Driver loaded");      // for debugging

            cnn = DriverManager.getConnection(strCnn);
            //System.out.println("Database connected"); // for debugging
           }
        catch(ClassNotFoundException ex){
            throw new DataAccessException("ClassNotFoundException thrown while loading driver class");
        }
        catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*************************************************************************/
    public static void close() throws DataAccessException{
        try{
            cnn.close();
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /*************************************************************************/
    public static boolean isOpened(){

        if(cnn==null) return false;
        
        try{
            return !cnn.isClosed();
        } catch (SQLException ex) {
            return false;
        }
    }
    /*************************************************************************/
    private static void testGetData() throws DataAccessException {

        try{
            CachedRowSet rs = new CachedRowSetImpl();
            rs.setUrl("jdbc:sqlserver://localhost\\SQLEXPRESS;Database=CelestialDB;integratedSecurity=true;");
            rs.setCommand("SELECT * FROM tblEmployees");
            rs.execute();

            while(rs.next()){
                // System.out.println(rs.getRow());
                System.out.println(rs.getString("EmployeeID") + "\t" + rs.getString("FirstName") + "\t"+
                rs.getString("LastName") +"\t" + rs.getString("Username"));
            }
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }   
    }
    /*************************************************************************/
    /* Helpers for inheriting classes*/
    protected static CachedRowSet getData(CallableStatement stm)
    throws DataAccessException{

        CachedRowSet crs;
        ResultSet rs;
        try{
             rs = stm.executeQuery();

             crs = new CachedRowSetImpl();
             crs.populate(rs);
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return crs;
    }
    /************************************************************************/
    protected static CachedRowSet getComplexData(CallableStatement stm)
    throws DataAccessException{

        CachedRowSet crs = null;
        ResultSet rs = null;

        int iResult = 0;
        try{
             while(true){
                 int iUpdate;
                 boolean bResult;

                 if(1 == ++iResult)
                     bResult = stm.execute();
                 else
                     bResult = stm.getMoreResults();

                 if(bResult)
                     rs = stm.getResultSet();
                 else {
                     iUpdate = stm.getUpdateCount();
                     if(iUpdate == -1){
                         iResult--;
                         break;
                     }
                 }
                 
             }
             if(!rs.isClosed()){
                crs = new CachedRowSetImpl();
                crs.populate(rs);
            }
        }catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
        return crs;
    }
    /************************************************************************/
    protected static <T> void fill(IFactory<? extends T> factory, 
    List<? super T> lst, CallableStatement stm)
    throws DataAccessException{

        /* clear list */
        lst.clear();

        /* grabs data */
        CachedRowSet crs = getData(stm);
        
        /*populate list */
        try{
            while(crs.next())
                lst.add(factory.createT(crs));
        }catch (Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /************************************************************************/
    protected static <K, T extends IKey<K>> void fill(IFactory<? extends T> factory,
    Dictionary<? super K, ? super T> dic, CallableStatement stm)
    throws DataAccessException{

        /* grabs data */
        CachedRowSet crs = getData(stm);

        /*populate dictionary */
        K key;
        T element; // some class that implements IKey: getKey()
        try{
            while(crs.next()){
                element = factory.createT(crs);
                key = element.getKey();
                dic.put(key, element);
            }
        }catch (Exception ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /************************************************************************/
    protected static CallableStatement prepareStatement(String call_usp) 
    throws DataAccessException{
        try{
            DataAccessBase.open();
            return cnn.prepareCall(call_usp);
        } catch(SQLException ex){
            throw new DataAccessException(ex.getMessage());
        }
    }
    /************************************************************************/
    /* Test */
    public static void main(String[] args){

        try{
            DataAccessBase.open();
            DataAccessBase.testGetData();

            DataAccessBase.close();
        }
        catch(DataAccessException ex){
            System.out.println(ex.getMessage() + ex.toString());
        }
    }
    /************************************************************************/
    
}
